"""
Case Type   : 向量数据库可靠性
Case Name   : ivfflat索引在构建过程注入备机kill-9故障
Create At   : 2025/9
Owner       : l1azzzy
Description :
    1、建表
    2、插入大量数据
    3、创建索引
    4、kill主机数据库进程
    5、重启数据库
    6、再次创建索引
    7、主备使用索引查询
    8、恢复初始环境
Expect      :
    1、建表成功
    2、插入数据成功
    3、创建索引成功
    4、kill主机数据库进程成功
    5、重启数据库成功
    6、再次创建索引成功
    7、主备使用索引查询成功
    8、环境恢复成功
History     :
"""

import os
import re
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

class DatavecIndexCase0001(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.user_node = [CommonSH('PrimaryDbUser'), CommonSH('Standby1DbUser'), CommonSH('Standby2DbUser')]
        self.pri_user = Node(node='PrimaryDbUser')
        self.com = Common('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_datavec_index_case0004'
        self.index_name = 'idx_datavec_index_case0004'
        self.log.info(f'----{os.path.basename(__file__)} end----')

    def test_datavec_index(self):
        text = '-----step1:建表  expect:建表成功-----'
        self.log.info(text)
        res = self.com.execut_db_sql(f'create table {self.table_name} (id bigserial primary key, val vector(160);')
        self.log.info(res)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res)

        text = '-----step2:插入数据  expect:插入数据成功-----'
        self.log.info(text)
        sql = f'''
                    do $$
                    declare
                    vector_dim int := 160;
                    vector_value float[];
                    record_num int := 20000;
                    j int;
                    begin
                    for j in 1..record_num loop
                    for i in 1..vector_dim loop
                    vector_value[i] := ROUND(RANDOM() * 100, 2);
                    end loop;
                    insert into {self.table_name} (val) values (vector_value);
                    end loop;
                    end $$;
                    '''
        res = self.com.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, res)

        text = '-----step3:创建索引  expect:创建索引成功-----'
        self.log.info(text)
        sql2 = f'''
        alter table t_relia_datavec_case set(parallel_workers=30);
        set maintenance_work_mem ='1GB';
        create index idx_t_relia_datavec_case on t_relia_datavec_case using ivfflat (val vector_l2_ops) with(lists=1000);
        '''
        res = self.com.execut_db_sql(sql2)
        self.log.info(res)
        self.assertIn(self.constant.INDEX_CREATE_SUCCESS, res)

        text = '-----step4:kill主机数据库进程  expect:kill主机数据库进程成功-----'
        self.log.info(text)
        res = self.pri_user.sh('ps ux|grep \'gaussdb \'|grep -v grep|awk \'{print $2}\'|xargs kill -9')
        self.log.info(res)
        
        text = '-----step5:重启数据库  expect:重启数据库成功-----'
        restart = self.user_node[0].restart_db_cluster(False)
        self.log.info(restart)
        self.assertTrue(restart)

        text = '-----step6:再次创建索引  expect:再次创建索引成功-----'
        self.log.info(text)
        res = self.com.execut_db_sql(sql2)
        self.log.info(res)
        self.assertIn(self.constant.INDEX_CREATE_SUCCESS, res)

        text = '-----step7:主备使用索引查询  expect:主备使用索引查询成功-----'
        self.log.info(text)
        sql3 = f'''
        set enable_indexscan=on;
        set enable_seqscan=off;
        select id,val<->'[33.91,98.59,85.29,15.32,4.9,
        78.51,15.83,1.63,39.63,52,40.46,74.03,16.95,95.42,
        15.85,56.45,28.41,69.76,55.21,68.63,53.24,75.3,5.87,
        42.23,26.98,81.97,89.8,58.99,0.04,85.03,73.05,1.27,
        59.57,79.81,87.65,81.77,34,3.57,6.12,34.42,43.21,93.49,
        81.3,64.9,87.38,0.36,2.03,2.05,54.29,43.58,62.91,76.71,
        47.7,14.25,6.93,9.56,8.46,23.97,24.78,84.65,86.24,55.93,
        2.43,50,73.33,23.49,15.29,44.7,94.62,56.72,8.84,36.69,
        50.54,69.76,62.04,41.83,84.16,64.97,53.27,22.08,22.78,
        83.52,85.29,54.41,68.86,47.64,42.35,65.58,59.54,64.85,61.89,
        21.02,9.73,89.1,87.04,82.73,15.58,93.47,3.27,46.71,45.06,91.6,
        49.84,80.88,35.7,14.65,78.72,97.65,70,73.04,23.79,62.36,0.7,99.44,
        50.06,98.07,56.67,25.9,6.77,55.16,10.47,13.38,82.87,61.39,25.64,
        48.81,4.9,13.26,2.41,1.46,65.29,58.76,89.64,86.72,84.13,13.96,
        45.98,98.53,95.92,52.29,90.25,19.42,90.89,68.13,37.44,4.48,
        99.26,98.01,40.22,18.98,78.94,50.45,42.93,21.05,89.19,53.91,
        65.14,56.68,12.69,51.74]' from t_relia_datavec_case 
        order by val<->'[33.91,98.59,85.29,15.32,4.9,78.51,15.83,1.63,
        39.63,52,40.46,74.03,16.95,95.42,15.85,56.45,28.41,69.76,55.21,
        68.63,53.24,75.3,5.87,42.23,26.98,81.97,89.8,58.99,0.04,85.03,
        73.05,1.27,59.57,79.81,87.65,81.77,34,3.57,6.12,34.42,43.21,
        93.49,81.3,64.9,87.38,0.36,2.03,2.05,54.29,43.58,62.91,76.71,
        47.7,14.25,6.93,9.56,8.46,23.97,24.78,84.65,86.24,55.93,2.43,50,
        73.33,23.49,15.29,44.7,94.62,56.72,8.84,36.69,50.54,69.76,62.04,
        41.83,84.16,64.97,53.27,22.08,22.78,83.52,85.29,54.41,68.86,47.64,
        42.35,65.58,59.54,64.85,61.89,21.02,9.73,89.1,87.04,82.73,15.58,
        93.47,3.27,46.71,45.06,91.6,49.84,80.88,35.7,14.65,78.72,
        97.65,70,73.04,23.79,62.36,0.7,99.44,50.06,98.07,56.67,25.9,
        6.77,55.16,10.47,13.38,82.87,61.39,25.64,48.81,4.9,13.26,2.41,
        1.46,65.29,58.76,89.64,86.72,84.13,13.96,45.98,98.53,95.92,52.29,
        0.25,19.42,90.89,68.13,37.44,4.48,99.26,98.01,40.22,18.98,78.94,
        50.45,42.93,21.05,89.19,53.91,65.14,56.68,12.69,51.74]' limit 10;
        '''
        res = self.com.execut_db_sql(sql3)
        self.log.info(res)
        self.assertIn(self.constant.SELECT_SUCCESS_MSG, res)

    def tearDown(self):
        text = '-----step8:清理表  expect:清理表成功-----'
        self.log.info(text)
        sql4 = f'''
        drop table if exists {self.table_name};
        '''
        res = self.com.execut_db_sql(sql4)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS_MSG, res)
        self.log.info(f'----{os.path.basename(__file__)} end----')